import pandas as pd
import numpy as np
import cx_Oracle
import xlrd
import json
import datetime

"""
IMEI 码由15位数字组成，其中第15位(IME15)是检验位，是通过将前14(IMEI14)运算得出的，

计算公式公式如下：

  IMEI15= 绝对值( (iSum+eSum) 除以10的余数 - 10 );

  IMEI14：从左到右分别为第1，2，3~14位。第14位为奇数位，第13位为偶数位

  其中,eSum = 7位偶数累加 

       iSum = 7位奇数位*2除以10的整数 累加 + 奇数位*2除以10的余数累加

************************************************************************************

IMEI 码生成以后，按鼠标右键可将 IMEI 码复制 或者保存成文字档案。 

"""
addday = 15*30
insertSql = "Insert Into CSC_GOODS_DELIVERY_RECORD(ID,CREATE_BY,CREATE_DATE,SYS_COMPANY_CODE,IMEI,IMEI2,SN,MATERIAL_CODE,MODEL,MANUFACTURE_DATE,WARRANTY_START_DATE,WARRANTY_END_DATE) Values (sys_guid(),'AdminImport',sysdate,'HY1',:imei,:imei2,:sn,:mtlCode,:model,:ccdate,:startdate,:enddate)"

#根据14IMEI生成15的IMEI
def generateImei(imei14,count):
    imei15list = []
    for i in range(count):
       
        imeiStartlist = list(str(imei14))   
        xisum = 0
        yisum = 0
        esum = 0
        #获取偶数位
        oslist = imeiStartlist[::2]
        #获取奇数位
        qslist = imeiStartlist[1::2]
        for qs in qslist :
            xisum = xisum + (int(int(qs) * 2 /10))
            yisum = yisum + (int(qs) * 2 % 10) 
        
        for q in  oslist:
            esum= esum + int(q)
        isum = xisum + yisum
        ex =abs(((isum+esum)%10)-10)
        if ex == 10:
            ex = 0
        imei15list.append(str(imei14)+str(ex))
        imei14 = imei14 + 1
    return imei15list
   
# 批量导入产品串号
def importCscGoods(importPath,dbconnection):
    df1 = pd.read_excel(importPath)
    for index,row in df1.iterrows():
        sku = row[1].strip()
        model= row[2].strip()
        nucount = row[4]
        range1 = row[5].strip()
        date1 = row[6]
        imeistart =range1.split("~")[0]
        imeiend =range1.split("~")[1]
        # print(imeistart,imeiend)
        imei15list = generateImei(int(imeistart),int(nucount))
        datalist = []
        localTime = datetime.datetime.strptime(str(date1),'%Y-%m-%d %H:%M:%S')
        endTime = localTime+datetime.timedelta(days=addday)
        for imei in imei15list:
            datalist.append({'imei':imei,'imei2':imei,'sn':imei,'mtlCode':sku,'model':model,'ccdate':localTime,'startdate':localTime,'enddate':endTime})
        dbconnection.insertRows(insertSql,datalist)

#数据库操作对象
class CscGoods1():
    def __init__(self,use,passwd,database):
        connection = cx_Oracle.connect(use,passwd,database,encoding="UTF-8")
        cursor = connection.cursor()
        print('初始化数据连接池成功！')
        self.connection = connection
        self.cursor = cursor
    def disconnect(self):
        self.cursor.close()
        self.connection.close()
    def insertRows(self,sql,dataLines):
        try:
            self.cursor.executemany(sql,dataLines)
            self.connection.commit()
            print("插入ok")
        except Exception as e:
            print(e)
            self.disconnect()
           

if __name__ == "__main__":
    # cscGoods = CscGoods1('CSC','IoTdev1905csc','10.100.21.122:1521/IOT_CSC')
    cscGoods = CscGoods1('CSC_IOT','IoTprd1912csc','platformdb02.iotscm.shbt.111.net:1521/IOT_CSC')
    importCscGoods(r"C:\Users\tanxiong\Desktop\20200929SN\HD1.xlsx",cscGoods)
    cscGoods.disconnect()
       
      
#        print(rangestartlist)
#        print(qslist,oslist)

#        print(rangestart,rangeend)    
#        print(sku,model,nucount,range1,date1)

#    cscGoods = CscGoods1('CSC','IoTdev1905csc','10.100.21.122:1521/IOT_CSC')
#    addday = 15*30
#    localTime = datetime.datetime.now()
#    endTime = localTime+datetime.timedelta(days=addday)
#    print(endTime)
#    insertSql = "Insert Into CSC.CSC_GOODS_DELIVERY_RECORD(ID,CREATE_BY,CREATE_DATE,SYS_COMPANY_CODE,IMEI,IMEI2,SN,MATERIAL_CODE,MODEL,MANUFACTURE_DATE,WARRANTY_START_DATE,WARRANTY_END_DATE) Values (sys_guid(),'AdminImport',sysdate,'HY1',:imei,:imei2,:sn,:mtlCode,:model,:ccdate,:startdate,:enddate)"
#    paramlist = [
#        {'imei':'IIII1111','imei2':'IIII1111','sn':'IIII1111','mtlCode':'Q000031','model':'X1','ccdate':localTime,'startdate':localTime,'enddate':endTime},
#        {'imei':'IIII11112','imei2':'IIII1112','sn':'IIII1112','mtlCode':'Q000031','model':'X1','ccdate':localTime,'startdate':localTime,'enddate':endTime}
#    ]

